﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Entity;

namespace DAL
{
    public class ProductDB : DBAccess
    {
        Product product;

        /// <summary>
        /// get all products
        /// </summary>
        /// <param name="productTypeId"></param>
        /// <returns></returns>
        public IList<Product> GetAll(int productTypeId)
        {
            this.Connect();
            IList<Product> listProduct = null;
            string sql;
            if (productTypeId == -1)
                //get all products
                sql = "SELECT  dbo.Products.*, dbo.ProductType.Name AS ProductTypeName,dbo.ProductType.TypeId AS TypeIDProduct, dbo.Units.Name AS UnitName FROM dbo.Products INNER JOIN dbo.ProductType ON dbo.Products.TypeId = dbo.ProductType.TypeId INNER JOIN dbo.Units ON dbo.Products.UnitId = dbo.Units.UnitId";
            else
                //get all products have Products.TypeId = productTypeId
                sql = "SELECT  dbo.Products.*, dbo.ProductType.Name AS ProductTypeName,dbo.ProductType.TypeId AS TypeIDProduct, dbo.Units.Name AS UnitName FROM dbo.Products INNER JOIN dbo.ProductType ON dbo.Products.TypeId = dbo.ProductType.TypeId INNER JOIN dbo.Units ON dbo.Products.UnitId = dbo.Units.UnitId WHERE Products.TypeId=" + productTypeId;

            SqlDataReader dr = this.Returncommand(sql);

            listProduct = new List<Product>();
            while (dr.Read())
            {
                product = new Product();
                Unit unit = new Unit();
                ProductType productType = new ProductType();

                product.Name = dr["name"].ToString();
                product.Price = decimal.Parse(dr["price"].ToString());
                product.ProductId = int.Parse(dr["productid"].ToString());
                product.Visible = bool.Parse(dr["visible"].ToString());


                productType.Name = dr["ProductTypeName"].ToString();
                productType.TypeId = int.Parse(dr["TypeIDProduct"].ToString());
                unit.UnitId = int.Parse(dr["UnitId"].ToString());
                unit.Name = dr["UnitName"].ToString();
                product.ProductType = productType;
                product.Unit = unit;

                listProduct.Add(product);
            }
            dr.Close();
            return listProduct;
        }

        /// <summary>
        /// return list product of product Type
        /// </summary>
        /// <param name="id"></param>
        /// <param name="_listProduct"></param>
        /// <returns></returns>
        public IList<Product> GetProductByTypeId(int id, IList<Product> _listProduct)
        {
            IList<Product> _listtemp = new List<Product>();

            foreach (Product item in _listProduct)
            {
                if (item.ProductType.TypeId == id)
                {
                    _listtemp.Add(item);
                }
            }
            return _listtemp;
        }

        /// <summary>
        /// return product when have product Id
        /// </summary>
        /// <param name="id">productId</param>
        /// <returns>product</returns>
        public Product GetProductById(int id)
        {
            Product _product = null;
            string sql = "SELECT * FROM products WHERE ProductID=" + id.ToString();
            SqlDataReader rd = ExecuteQuery(sql, CommandType.Text);
            if (rd.Read())
            {
                _product = new Product();
                ProductType _productType = new ProductType();
                Unit _unit = new Unit();
                _product.ProductId = id;
                _product.Name = rd["Name"].ToString();
                _product.Price = decimal.Parse(rd["Price"].ToString());
                _product.Visible = bool.Parse(rd["Visible"].ToString());
                _productType.TypeId = int.Parse(rd["TypeId"].ToString());
                _product.ProductType = _productType;
                _unit.UnitId = int.Parse(rd["UnitId"].ToString());
                _product.Unit = _unit;
                rd.Close();
                return _product;
            }
            else
            {
                rd.Close();
                return _product;
            }
        }

        //insert product
        public void AddProduct(string name, int unitId, decimal price, int typeId, int visible)
        {
            ExecuteNonQuery("sp_addproduct", CommandType.StoredProcedure, "@name", name, "@unitId", unitId, "@price", price, "@typeId", typeId, "@visible", visible);
        }

        /// <summary>
        /// Update a product
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="unitId"></param>
        /// <param name="price"></param>
        /// <param name="typeId"></param>
        /// <param name="visible"></param>
        public void UpdateProduct(int id, string name, int unitId, decimal price, int typeId, int visible)
        {
            ExecuteNonQuery("sp_updateProduct", CommandType.StoredProcedure, "@id", id, "@name", name, "@unitId", unitId, "@price", price, "@typeId", typeId, "@visible", visible);
        }

        /// <summary>
        /// Delete a product
        /// </summary>
        /// <param name="id"></param>
        public void DeleteProduct(int id)
        {
            ExecuteNonQuery("Sp_delProduct", CommandType.StoredProcedure, "id", id);
        }
    }
}
